﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DBAccess;
using SSPharma.Common;
using SSPharma.Model;
using System.Data.Common;
using SSPharma.Biz.Common;
using System.Transactions;

namespace SSPharma.Biz.DanhMuc
{
    class BizDanhMucTo
    {
        //khai báo hàm Common dùng để lấy các hàm dùng chung
        private BizCommon _BizCommon = new BizCommon();
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// lấy tổ theo mã tổ
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public DataTable ft_GetDataDanhMucToSanXuatTheoMa(string Ma)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT a.*,B.TenDonVi,c.TenBoPhan from HT_DM_To a with(nolock) left outer join HT_DM_DonVi b on a.MaDonVi = b.MaDonVi");
            strSQL.Append(" left outer join HT_DM_BoPhan c on a.MaBoPhan = c.MaBoPhan");
            strSQL.Append(" where MaTo = @Ma");
            SQLServer.Param[] Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@Ma", Ma);
            return Global.DataFactory.GetDataTable(strSQL.ToString(), Params);
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Lay cau truc de luu du lieu xong database
        /// </summary>
        /// <returns></returns>
        public DataSet fs_LayCauTrucTable()
        {
            DataSet dsdata = new DataSet();
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT a.*,B.TenDonVi,c.TenBoPhan from HT_DM_To a left outer join HT_DM_DonVi b on a.MaDonVi = b.MaDonVi");
            strSQL.Append(" left outer join HT_DM_BoPhan c on a.MaBoPhan = c.MaBoPhan where 1=2");
            DataTable dt_ToSanXuat = Global.DataFactory.GetDataTable(strSQL.ToString());
            dt_ToSanXuat.TableName = "dt_ToSanXuat";
            dsdata.Tables.Add(dt_ToSanXuat);
            strSQL.Clear();
            strSQL.Append(" SELECT MaDonVi,TenDonVi from HT_DM_DonVi where MaDonVi=@MaDonVi");
            SQLServer.Param[] Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@MaDonVi", Global.DonVi);
            DataTable dt_DonVi = Global.DataFactory.GetDataTable(strSQL.ToString(), Params);
            dt_DonVi.TableName = "dt_DonVi";
            dsdata.Tables.Add(dt_DonVi);
            return dsdata;
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Them danh muc tổ
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="TienTo"></param>
        public void fg_InsertDanhMucToSanXuat(DataTable dt, string TienTo)
        {
            try
            {
                string query_ToSanXuat = "Select * from HT_DM_To where 1 = 2";
                using (TransactionScope scope = new TransactionScope())
                {
                    Global.DataFactory.RunSaveData(dt, query_ToSanXuat);
                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                throw new System.Configuration.Provider.ProviderException(ex.Message);
            }
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Cap nhat danh muc tổ sản xuất
        /// </summary>
        /// <param name="dt"></param>
        public void fg_UpdateDanhMucToSanXuat(DataTable dt)
        {
            try
            {
                string query_ToSanXuat = "Select * from HT_DM_To where 1 = 2";
                using (TransactionScope scope = new TransactionScope())
                {
                    Global.DataFactory.RunSaveData(dt, query_ToSanXuat);
                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                throw new System.Configuration.Provider.ProviderException(ex.Message);
            }
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Mục đích: Lấy danh sách tổ sản xuất cho combobox tổ sản xuất
        /// </summary>
        /// <returns></returns>
        public IList<ToSanXuat> fl_DSDanhMuc_ToSanXuat()
        {
            List<ToSanXuat> items = new List<ToSanXuat>();
            ToSanXuat item;
            StringBuilder strSQL = new StringBuilder(300);
            strSQL.Append("SELECT MaTo,TenTo");
            strSQL.Append(" FROM HT_DM_To");
            strSQL.Append(" ORDER BY ID_SQ");
            using (DbDataReader dataReader = Global.DataFactory.Reader(strSQL.ToString()))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        item = new ToSanXuat();
                        item.Id = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0);
                        item.Name = dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1);
                        items.Add(item);
                    }
                }
            }

            return items;
        }
        /// <summary>
        /// User: TAMNN 23/07/2014
        /// Mục đích: lấy danh sách theo danh mục
        /// </summary>
        /// <returns></returns>
        public DataTable ft_GetListdata(IList<string> list, string Orderby)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT a.ID_SQ,a.MaTo,a.TenTo,c.MaBoPhan,c.TenBoPhan,SoNV,a.MaDonVi,a.GhiChu,a.Active");
            strSQL.Append(" from HT_DM_To a with(nolock) left outer join HT_DM_DonVi b on a.MaDonVi = b.MaDonVi");
            strSQL.Append(" left outer join HT_DM_BoPhan c on a.MaBoPhan = c.MaBoPhan");
            strSQL.Append(!string.IsNullOrEmpty(Orderby) ? (" order by " + Orderby) : "");
            return Global.DataFactory.GetDataTable(strSQL.ToString());
        }
         /// <summary>
        /// User:TAMNN 28/07/2014 
        /// Mục đích: kiểm tra mã tổ đã tồn tại hay chưa
        /// </summary>
        /// <param name="p_sTableName"></param>
        /// <param name="p_sfill"></param>
        /// <param name="p_sGiatri"></param>
        /// <param name="p_sDieuKien"></param>
        /// <returns></returns>
        public string fs_CheckDataExistsToSanXuat(string p_sGiatri)
        {
            return _BizCommon.fs_LayDuLieuTrongTable("HT_DM_To", "MaTo", "MaTo  = N'" + p_sGiatri +"'");
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Lấy tên bộ phận cha
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetTenBoPhanTheoMa(string Ma)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT TenBoPhan from HT_DM_BoPhan a with(nolock)");
            strSQL.Append(" where a.MaBoPhan = @Ma");
            SQLServer.Param[] Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@Ma", Ma);


            using (DbDataReader dataReader = Global.DataFactory.Reader(strSQL.ToString(), Params))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        return dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0);
                    }
                }
            }
            return "";
        }
        /// <summary>
        /// User: TAMNN 21/07/2014
        /// Lấy tên đơn vị
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public string ft_GetTenDonViTheoMa(string Ma)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT TenDonVi from HT_DM_DonVi a with(nolock)");
            strSQL.Append(" where a.MaDonVi = @Ma");
            SQLServer.Param[] Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@Ma", Ma);


            using (DbDataReader dataReader = Global.DataFactory.Reader(strSQL.ToString(), Params))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        return dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0);
                    }
                }
            }
            return "";
        }
    }
}
